﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;

public partial class admin_QueryGoods : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
          {
           // BindData();
           }
    }
    private void BindData()
     {
         string strCon = "server=.;database=keshe2;Integrated Security=SSPI";
         string strSql = "select * from 商品";
         SqlDataAdapter da = new SqlDataAdapter(strSql, strCon);
         DataTable dt = new DataTable();
         da.Fill(dt);
         this.GridView1.DataSource = dt;
         //this.GridView1.DataBind();
 
     }

    //数据绑定后触发
    protected void GridView1_RowDataBound(object sender, GridViewRowEventArgs e)
     {
        //如果行的类型是数据绑定行
        if (e.Row.RowType == DataControlRowType.DataRow)
        {
            //行的状态是：正常状态 或者 交替行
            if (e.Row.RowState == DataControlRowState.Normal || e.Row.RowState == DataControlRowState.Alternate)
            {
                Label lbl = e.Row.FindControl("Label1") as Label;
            }
        }
        //行的状态是： 编辑状态 或者 （交替行且是编辑状态）
         if (e.Row.RowState == DataControlRowState.Edit || e.Row.RowState == (DataControlRowState.Alternate | DataControlRowState.Edit))
        {
        if ((DropDownList)e.Row.FindControl("DropDownList1") != null)
        {
            DropDownList ddl = e.Row.FindControl("DropDownList1") as DropDownList;
                // if (ddl != null)
                //{
                 ListItem item1 = new ListItem();
                 item1.Value = "A";
                 item1.Text = "酒水调料";
                 ddl.Items.Add(item1);

                 ListItem item2 = new ListItem();
                 item2.Value = "B";
                 item2.Text = "冷冻食品";
                 ddl.Items.Add(item2);

                 ListItem item3 = new ListItem();
                 item3.Value = "C";
                 item3.Text = "生鲜水果";
                 ddl.Items.Add(item3);

                 ListItem item4 = new ListItem();
                 item4.Value = "D";
                 item4.Text = "洗漱日化";
                 ddl.Items.Add(item4);

                 ListItem item5 = new ListItem();
                 item5.Value = "E";
                 item5.Text = "休闲零食";
                 ddl.Items.Add(item5);
                //}
               
                  string strSQL = "select * from 商品类型";
                  DataSet ds = GetDataSet(strSQL);
                  ddl.DataSource = ds;
                  ddl.DataTextField = "名称";
                  ddl.DataValueField = "编号";
                  ddl.DataBind();
            }
        }
      }



    public static DataSet GetDataSet(string sql)
    {
        String strConnection = "server=.;database=keshe2;Integrated Security=SSPI";
        SqlConnection conn = new SqlConnection(strConnection);  //实例化SqlConnection对象
        DataSet ds = new DataSet();  //实例化DataSet
        conn.Open();    //打开数据库连接
        SqlDataAdapter sda = new SqlDataAdapter(sql, conn);   //sql是传递过来的SQL语句
        sda.Fill(ds);  //填充DataSet
        return ds;   //返回DataSet
    }





   /* protected void BindGridView1()
    {
        string sql = "select * from 商品";
        DataSet ds = GetDataSet(sql);
        GridView1.DataSource = ds;
        //GridView1.DataBind();
        for (int i = 0; i < GridView1.Rows.Count; i++)
        {
           // string sqlCollege = "select 商品编号 from 商品 where 商品编号=" + GridView1.DataKeys[i].ToString();
            string cid = ds.Tables[0].Rows[i]["编号"].ToString();
            ((DropDownList)GridView1.Rows[i].Cells[1].FindControl("DropDownList1")).SelectedValue = cid;
        }
    }



    //DataOperate为数据库操作函数
    //DataOperate sqlHelper = new DataOperate();

    /// <summary>
    /// 绑定GridView1
    /// </summary>

    protected void GridView1_RowCancelingEdit(object sender, GridViewCancelEditEventArgs e)
     {
         GridView1.EditIndex = -1;
         BindGridView1();
         //GridView1.DataBind();
     }

     protected void GridView1_RowDataBound(object sender, GridViewRowEventArgs e)
     {
         if ((DropDownList)e.Row.FindControl("DropDownList1") != null)
         {
             String strConnection = "server=.;database=keshe2;Integrated Security=SSPI";
             SqlConnection connection = new SqlConnection(strConnection);
             connection.Open();
             DropDownList ddl = (DropDownList)e.Row.FindControl("DropDownList1");
             string strSQL = "select * from 商品类型";
             SqlCommand command = new SqlCommand(strSQL, connection);
             SqlDataReader Dr = command.ExecuteReader();
             try
             {
                 while (Dr.Read())
                 {
                     DataSet ds = GetDataSet(strSQL);
                     ddl.DataSource = ds;
                     ddl.DataTextField = Dr["名称"].ToString();
                     ddl.DataValueField = Dr["编号"].ToString();
                     ddl.DataBind();
                 }
             }
             catch (SqlException ex)
             {
                 Response.Write(ex.Message.ToString());
             }
             finally
             {
                 Dr.Close();
             }


         }
     }

     protected void GridView1_RowEditing(object sender, GridViewEditEventArgs e)
     {
         GridView1.EditIndex = e.NewEditIndex;
         BindGridView1();
         //GridView1.DataBind();
     }

     protected void GridView1_RowUpdating(object sender, GridViewUpdateEventArgs e)
     {
         string cid = ((DropDownList)GridView1.Rows[e.RowIndex].FindControl("DropDownList1")).SelectedValue.ToString();
         string stuID = GridView1.DataKeys[e.RowIndex].Value.ToString();
         string cmdText = "Update 商品 set 商品类型=" + cid + " where 商品编号" + stuID;
         //sqlHelper.ExecCmd(cmdText);
         if (Insert(cmdText) == 1)
         {
             GridView1.EditIndex = -1;
             BindGridView1();
             //GridView1.DataBind();
         }
     }
     public static int Insert(string sql)
     {
         String strConnection = "server=.;database=keshe2;Integrated Security=SSPI";
         SqlConnection conn = new SqlConnection(strConnection);
         conn.Open();
         SqlCommand cmd = new SqlCommand(sql, conn);
         int a;
         try
         {
             a = cmd.ExecuteNonQuery();
         }
         catch
         {
             a = 0;
         }
         conn.Close();
         return a;
     }*/

    protected void sdsQueryGoods_Selecting(object sender, SqlDataSourceSelectingEventArgs e)
    {

    }

    protected void GridView1_SelectedIndexChanged(object sender, EventArgs e)
    {

    }

    protected void btnQuery_Click(object sender, EventArgs e)
    {
        //string sql = "SELECT * FROM 商品";
        string sql = "SELECT 商品.商品编号, 商品.商品名称, 商品.数量, 商品.图片, 商品.价格, 商品类型.名称 FROM 商品 INNER JOIN 商品类型 ON 商品.商品类型 = 商品类型.编号";
        if (TextBox1.Text.Trim().Length != 0)
            sql = sql + " WHERE " + DropDownList2.SelectedValue + " like '%" +TextBox1.Text+ "%'";
        //sql = sql + SELECT 商品.商品编号, 商品.商品名称, 商品.数量, 商品.图片, 商品.价格, 商品类型.名称 FROM 商品 INNER JOIN 商品类型 ON 商品.商品类型 = 商品类型.编号
        String strConnection = "Server=.;Database=keshe2;Integrated Security=SSPI";
        SqlConnection con = new SqlConnection(strConnection);
        con.Open();
        SqlConnection sqlcon = new SqlConnection(strConnection);
        sqlcon.Open();
        SqlCommand sqlcom = new SqlCommand(sql, sqlcon);
        SqlDataReader Dr = sqlcom.ExecuteReader();

        if (Dr.HasRows)
        {
            GridView1.DataSourceID = "sdsQueryGoods";
            sdsQueryGoods.SelectCommand = sql;
             sdsQueryGoods.Select(DataSourceSelectArguments.Empty);
        }
        else
        {
            GridView1_empty();
        }
        Dr.Close();
        sqlcon.Close();

    }
        protected void GridView1_empty()
        {
            DataTable dt = new DataTable();
            dt.Columns.Add("商品编号");
            dt.Columns.Add("商品名称");
            dt.Columns.Add("数量");
            dt.Columns.Add("价格");
            dt.Columns.Add("名称");
            dt.Columns.Add("图片");
            dt.Columns.Add("商品图片");
        if (dt.Rows.Count == 0)
            {
                dt.Rows.Add(dt.NewRow());
                GridView1.DataSourceID = "";
                GridView1.DataSource = dt;
                GridView1.DataBind();
                int columnCount = dt.Columns.Count;
                GridView1.Rows[0].Cells.Clear();
                GridView1.Rows[0].Cells.Add(new TableCell());
                GridView1.Rows[0].Cells[0].ColumnSpan = columnCount;
                GridView1.Rows[0].Cells[0].Text = "暂无该商品记录！";
                GridView1.Rows[0].Cells[0].Style.Add("text-align", "center");
            }

        }
}